package com.demo.easyExcel;


import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @author ：Howin
 * @Describe 使用POI写数据
 * @date ：Created in 2021/7/24 15:45
 */
public class POIWrite {
    String Path = "D:\\project\\POI_EasyExcel\\src\\main\\resources\\";

    @Test
    public void demo() throws IOException {
        Workbook hssfWorkbook = new HSSFWorkbook();
        Sheet sheet = hssfWorkbook.createSheet("学生成绩表");
        sheet.createRow(0).createCell(0).setCellValue(2021+"年期末学生成绩表");
        String time = new DateTime().toString("yyyy-MM-dd hh:mm:ss");
        sheet.createRow(1).createCell(0).setCellValue(time);
        FileOutputStream stream = new FileOutputStream(Path + "学生成绩表.xls");
        hssfWorkbook.write(stream);
        stream.close();
        System.out.println("Over!");
    }

    @Test
    public void test03() throws IOException {
        //查看一个接口的实现类ctrl+alt+B     ctrl +H 查看继承关系
        long start = System.currentTimeMillis();

        Workbook sheets = new HSSFWorkbook();
        Sheet sheet = sheets.createSheet("第一个测试");
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        FileOutputStream stream = new FileOutputStream(Path+"test03.xls");
        sheets.write(stream);
        stream.close();
        long end = System.currentTimeMillis();

        System.out.println(end- start);
    }
    /*
    耗时较长,插入数据较长,此时可以升级一下
    使用SXSSFWorkbook ,他会有个临时文件,每次缓存100条到临时文件,再写,提高效率
    07版本的最大行1048575行
     */
    @Test
    public void test07() throws IOException {
        //查看一个接口的实现类ctrl+alt+B     ctrl +H 查看继承关系
        long start = System.currentTimeMillis();

        Workbook sheets = new XSSFWorkbook();
        Sheet sheet = sheets.createSheet("07版本的测试");
        for (int i = 0; i < 65539; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        FileOutputStream stream = new FileOutputStream(Path+"test07.xlsx");
        sheets.write(stream);
        stream.close();
        long end = System.currentTimeMillis();

        System.out.println(end- start);
    }

    @Test
    public void test07Plus() throws IOException {
        //查看一个接口的实现类ctrl+alt+B     ctrl +H 查看继承关系
        long start = System.currentTimeMillis();

        Workbook sheets = new SXSSFWorkbook();
        Sheet sheet = sheets.createSheet("07版本的plus测试,解决大数据插入慢的问题");
        for (int i = 0; i < 1048575; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        FileOutputStream stream = new FileOutputStream(Path+"test07_Plus.xlsx");
        sheets.write(stream);
        stream.close();
        //清理临时文件
        ((SXSSFWorkbook) sheets).dispose();
        long end = System.currentTimeMillis();

        System.out.println(end- start);
    }
}
